package com.cty.dao.impl;

import com.cty.dao.UserDao;
import com.cty.pojo.User;
import org.apache.log4j.Logger;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @Auther: cty
 * @Date: 2020/2/22 10:36
 * @Description:
 * @version: 1.0
 */
public class UserDaoImpl implements UserDao {
    static Logger logger = Logger.getLogger(UserDaoImpl.class);

    public static final String URL = "jdbc:mysql://localhost:3306/manager";
    public static final String USER = "root";
    public static final String PWD = "123456";

    /**
     * 获取用户数据进行用户校验
     * @param uname
     * @param pwd
     * @return
     */
    @Override
    public User checkUserLoginDao(String uname, String pwd) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            // 1 加载mysql驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            // 2 获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PWD);

            // 3 操作数据库，实现增删改查
            String sql = "select * from user where uname=? and pwd=?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, uname);
            ps.setObject(2, pwd);
            rs = ps.executeQuery();

            // 4 处理返回数据
            while (rs.next()){
                user = new User();
                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setPwd(rs.getString("pwd"));
                user.setSex(rs.getInt("sex"));
                user.setAge(rs.getInt("age"));
                user.setBirth(rs.getString("birth"));
            }
        } catch (ClassNotFoundException e) {
            logger.error("找不到mysql驱动类！");
            e.printStackTrace();
        } catch (SQLException e) {
            logger.error("无法获取数据库连接对象！");
            e.printStackTrace();
        } finally {
            // 5 关闭连接
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error("ResultSet关闭失败！");
                e.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error("PreparedStatement关闭失败！");
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("Connection关闭失败！");
                e.printStackTrace();
            }
        }
        return user;
    }

    /**
     * 用户密码修改
     * @param uid
     * @param newPwd
     * @return
     */
    @Override
    public int userChangePwd(int uid, String newPwd) {
        Connection conn = null;
        PreparedStatement ps = null;
        int index = -1;
        try {
            // 1 加载mysql驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2 建立数据库连接
            conn = DriverManager.getConnection(URL, USER, PWD);
            // 3 操作数据库，实现增删改查
            // 书写sql语句
            String sql = "update user set pwd=? where uid=?";
            // 获取ps对象
            ps = conn.prepareStatement(sql);
            // 设置占位符
            ps.setObject(1, newPwd);
            ps.setObject(2, uid);
            // 执行改操作
            index = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            logger.error("找不到mysql驱动类！");
            e.printStackTrace();
        } catch (SQLException e) {
            logger.error("无法获取数据库连接对象！");
            e.printStackTrace();
        }finally {
            // 关闭资源
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error("PreparedStatement关闭失败！");
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("Connection关闭失败！");
                e.printStackTrace();
            }
        }
        // 返回更新的行数
        return index;
    }

    /**
     * 获取所有用户信息
     * @return
     */
    @Override
    public List<User> userShowDao() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> ul = null;
        try {
            // 1 加载mysql驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            // 2 获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PWD);

            // 3 操作数据库，实现增删改查
            String sql = "select * from user";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            // 4 处理返回数据
            ul = new ArrayList<User>();
            while (rs.next()){
                User user = new User();
                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setPwd(rs.getString("pwd"));
                user.setSex(rs.getInt("sex"));
                user.setAge(rs.getInt("age"));
                user.setBirth(rs.getString("birth"));
                ul.add(user);
            }
        } catch (ClassNotFoundException e) {
            logger.error("找不到mysql驱动类！");
            e.printStackTrace();
        } catch (SQLException e) {
            logger.error("无法获取数据库连接对象！");
            e.printStackTrace();
        } finally {
            // 5 关闭连接
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error("ResultSet关闭失败！");
                e.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error("PreparedStatement关闭失败！");
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("Connection关闭失败！");
                e.printStackTrace();
            }
        }
        return ul;
    }

    /**
     * 将注册用户信息存入数据库
     * @param req
     * @param resp
     * @return
     */
    @Override
    public int userRegDao(HttpServletRequest req, HttpServletResponse resp) {
        Connection conn = null;
        PreparedStatement ps = null;
        int index = -1;
        try {
            // 1 加载mysql驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            // 2 获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PWD);

            // 3 操作数据库，实现增删改查
            String sql = "insert into user values (default ,?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, ((User)req.getAttribute("user")).getUname());
            ps.setObject(2, ((User)req.getAttribute("user")).getPwd());
            ps.setObject(3, ((User)req.getAttribute("user")).getSex());
            ps.setObject(4, ((User)req.getAttribute("user")).getAge());
            ps.setObject(5, ((User)req.getAttribute("user")).getBirth());
            index = ps.executeUpdate();

            // 4 处理返回数据
        } catch (ClassNotFoundException e) {
            logger.error("找不到mysql驱动类！");
            e.printStackTrace();
        } catch (SQLException e) {
            logger.error("无法获取数据库连接对象！");
            e.printStackTrace();
        } finally {
            // 5 关闭连接
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error("PreparedStatement关闭失败！");
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("Connection关闭失败！");
                e.printStackTrace();
            }
        }
        return index;
    }
}
